﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Web.Configuration;
using System.Data.SqlClient;

/// <summary>
/// Summary description for TourManagement
/// </summary>
public class TourManagement
{
    #region const declaration
    public const string TABLE_NAME = "Tours";
    public const string TOURID = "tourID";
    public const string TOURCODE = "tourCode";
    public const string TOURNAME = "tourName";
    public const string RACE = "race";
    public const string PLACESTART = "placeStart";
    public const string PLACEEND = "placeEnd";
    public const string STARTDATE = "startDate";
    public const string ENDDATE = "endDate";
    public const string INTERVALTIME = "intervalTime";
    public const string PRICE = "price";
    public const string SELLPRICE = "sellPrice";
    public const string ITINERARY = "itinerary";
    public const string PLANTOUR = "planTour";
    public const string STATUS = "status";
    #endregion


	public TourManagement()
	{
	}

    public static int AddTour(string tourCode, string tourName, int race, string placeStart, string placeEnd, DateTime startDate,
         DateTime endDate, float intervalTime, float price, float sellPrice, string itinerary, string planTour, bool status, List<string> pathLink)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        cnn.Open();
    
        string sqlQuery = "INSERT INTO Tours Values(@tourCode,@tourName,@race,@placeStart,@placeEnd,@startDate,@endDate,@intervalTime,@price,@sellPrice,@itinerary,@planTour,@status);SELECT scope_identity()";
        SqlCommand command = new SqlCommand(sqlQuery, cnn);
        command.Parameters.AddWithValue("@tourCode", tourCode);
        command.Parameters.AddWithValue("@tourName", tourName);
        command.Parameters.AddWithValue("@race", race);
        command.Parameters.AddWithValue("@placeStart", placeStart);
        command.Parameters.AddWithValue("@placeEnd", placeEnd);
        command.Parameters.AddWithValue("@startDate", startDate);
        command.Parameters.AddWithValue("@endDate", endDate);
        command.Parameters.AddWithValue("@intervalTime", intervalTime);
        command.Parameters.AddWithValue("@price", price);
        command.Parameters.AddWithValue("@sellPrice", sellPrice);
        command.Parameters.AddWithValue("@itinerary", itinerary);
        command.Parameters.AddWithValue("@planTour", planTour);
        command.Parameters.AddWithValue("@status", status);

        int n = int.Parse(command.ExecuteScalar().ToString());
        cnn.Close();

        if (n > 0)
        {
            //add images for tour
            for (int i = 0; i < pathLink.Count; i++)
                ImageManagement.AddImg(pathLink[i], Common.IMAGE_TOUR, n);
        }
            
        return n;
    }

    public static bool EditTour(int tourID, string tourCode, string tourName, int race, string placeStart, string placeEnd,
        DateTime startDate, DateTime endDate, float intervalTime, float price, float sellPrice, string itinerary, string planTour, bool status)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        cnn.Open();

        string sqlQuery = "UPDATE Tours Set tourCode =@tourCode, tourName = @tourName, race=@race,placeStart=@placeStart,placeEnd=@placeEnd,"
            + "startDate=@startDate,endDate=@endDate,intervalTime=@intervalTime,price=@price,sellPrice=@sellPrice,itinerary=@itinerary,planTour=@planTour,status=@status"
            +" WHERE tourID=@tourID";
        SqlCommand command = new SqlCommand(sqlQuery, cnn);
        command.Parameters.AddWithValue("@tourCode", tourCode);
        command.Parameters.AddWithValue("@tourName", tourName);
        command.Parameters.AddWithValue("@race", race);
        command.Parameters.AddWithValue("@placeStart", placeStart);
        command.Parameters.AddWithValue("@placeEnd", placeEnd);
        command.Parameters.AddWithValue("@startDate", startDate);
        command.Parameters.AddWithValue("@endDate", endDate);
        command.Parameters.AddWithValue("@intervalTime", intervalTime);
        command.Parameters.AddWithValue("@price", price);
        command.Parameters.AddWithValue("@sellPrice", sellPrice);
        command.Parameters.AddWithValue("@itinerary", itinerary);
        command.Parameters.AddWithValue("@planTour", planTour);
        command.Parameters.AddWithValue("@status", status);
        command.Parameters.AddWithValue("@tourID", tourID);

        int n = command.ExecuteNonQuery();

        cnn.Close();

        if (n == 1)
            return true;
        else
            return false;
    }

    public static bool DeleteTour(int tourID)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        cnn.Open();

        string sqlQuery = "DELETE Tours WHERE tourID=@tourID";
        SqlCommand command = new SqlCommand(sqlQuery, cnn);
        command.Parameters.AddWithValue("@tourID", tourID);

        int n = command.ExecuteNonQuery();

        if (n == 0)
            return false;
        else
            return true;
    }

    public static DataSet GetAllTour()
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);

        //string sqlQuery = "SELECT T.*, TH.numRoom, H.hotelName, H.hotelID FROM Tours T JOIN Tour_Hotel TH ON T.tourID = TH.tourID JOIN Hotels H ON TH.hotelID=H.hotelID ORDER BY T.tourName,startDate DESC";
        string sqlQuery = "SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY T.tourName,startDate DESC) AS NO, T.*, TH.numRoom, H.hotelName, H.hotelID FROM Tours T JOIN Tour_Hotel TH ON T.tourID = TH.tourID JOIN Hotels H ON TH.hotelID=H.hotelID WHERE T.status = 1) AS SUB_SQL";
        SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery, cnn);

        DataSet dsAllTour = new DataSet();
        adapter.Fill(dsAllTour, "AllTour");

        return dsAllTour;
    }

    public static DataTable GetTourByID(int tourID)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);

        string sqlQuery = "SELECT T.*, TH.numRoom, TH.departDate, TH.returnDate,TH.intervalTime night, H.* FROM Tours T JOIN Tour_Hotel TH ON T.tourID = TH.tourID JOIN Hotels H ON TH.hotelID=H.hotelID WHERE T.status = 1 AND T.tourID=" + tourID;
        SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery, cnn);

        DataSet dsAllTour = new DataSet();
        adapter.Fill(dsAllTour, "TourByID");

        return dsAllTour.Tables["TourByID"];
    }

    public static DataSet GetAllTourFromPR(string from, string to, string departDate, string returnDate)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        SqlCommand command = new SqlCommand("PR_LIST_TOUR", cnn);
        command.CommandType = CommandType.StoredProcedure;

        command.Parameters.Add("@from", from);
        command.Parameters.Add("@to", to);
        command.Parameters.Add("@departDate", departDate);
        command.Parameters.Add("@returnDate", returnDate);

        SqlDataAdapter adapter = new SqlDataAdapter(command);

        DataSet dsAllTour = new DataSet();
        adapter.Fill(dsAllTour, "AllTour");

        return dsAllTour;
    }

    public static DataSet GetAllOtherTour(int tourID)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        string sqlQuery = "SELECT T.*, TH.numRoom, H.hotelName, H.hotelID FROM Tours T JOIN Tour_Hotel TH ON T.tourID = TH.tourID JOIN Hotels H ON TH.hotelID=H.hotelID WHERE T.status = 1 AND T.tourID not like '" + tourID + "' ";

        SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery, strCon);

        DataSet dsTours = new DataSet();
        adapter.Fill(dsTours, "Tours");

        return dsTours;
    }

    public static DataSet SearchTour(string from, string to, DateTime departDate, DateTime returnDate)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);

        //string sqlQuery = "SELECT T.*, TH.numRoom, H.hotelName, H.hotelID FROM Tours T JOIN Tour_Hotel TH ON T.tourID = TH.tourID JOIN Hotels H ON TH.hotelID=H.hotelID ";
        string sqlSearch = "T.*, TH.numRoom, H.hotelName, H.hotelID FROM Tours T JOIN Tour_Hotel TH ON T.tourID = TH.tourID JOIN Hotels H ON TH.hotelID=H.hotelID";
        string strWhere = " WHERE 1=1 ";
        if (from.Length > 0)
            strWhere += " AND T.placeStart LIKE '%"+from+ "%'";
        if (to.Length > 0)
            strWhere += " AND T.placeEnd LIKE '%" + to + "%'";
        if (departDate != null && departDate != DateTime.MinValue)
            strWhere += " AND CONVERT(varchar(20),T.startDate,112) >= " + Util.ConvertDateToString(departDate);
        if (returnDate != null && returnDate != DateTime.MinValue)
            strWhere += " AND CONVERT(varchar(20),T.endDate,112) <= " + Util.ConvertDateToString(returnDate);
        string sqlQuery = "SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY T.tourID) AS NO, " + sqlSearch + strWhere + " ) AS SUB_SQL ";

        SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery, cnn);

        DataSet dsAllTour = new DataSet();
        adapter.Fill(dsAllTour, "TourSearch");

        return dsAllTour;
    }
}